The approaches described in this section are approaches that could be pursued, but not necessarily approaches that have been previously conceived or pursued. Therefore, unless otherwise indicated, it should not be assumed that any of the approaches described in this section qualify as prior art merely by virtue of their inclusion in this section.
The data in relational and object-relational databases is logically organized in database objects (sometimes also referred to as “schema objects”) that may include, but are not limited to, tables, views, sequences, stored procedures, indexes, clusters, and database links to other databases. Physically, the data of such databases is stored on persistent storage devices in data units (sometimes referred to as “data blocks”) that are configured to store a specific number of data bytes; in various embodiments and implementations, such data units may be physically stored as raw blocks directly on the persistent storage devices or may be stored in files that are stored on the persistent stored devices. For example, the data of a relational table may be logically organized in records, where each record includes one or more fields for storing data values from the corresponding one or more columns that are configured for the table. Physically, the data values in each data record may be stored as a data row in one or more data blocks that are stored in a file on a persistent storage device such as a hard disk.
Typically, users and applications access the data of relational and object-relational databases by using queries and requests that reference the database objects that store the requested data. For example, a user or an application may send a query to a database server, where the query specifies one or more operations and one or more relational tables that store the data on which the operations are to be performed. In response to the query, the database server determines the data block(s) in which the data rows having the requested data are physically stored, copies these data blocks(s) from the persistent storage devices into a buffer cache in volatile memory if these data block(s) are not already in the buffer cache, and performs the operations specified in the query on the data rows in the copies of the data block(s) that are stored in the buffer cache. The database server may use various memory management mechanisms to flush any modified (also referred to as “dirty”) data blocks from the buffer cache to the persistent storage devices in order to free up space for other data blocks that need to be read into the buffer cache.
Most persistent storage devices (e.g., such as optical or electromagnetic disks) that are used to store database data typically operate by using block-level operations that read and write data in blocks that are larger in size than a single byte or even several bytes. For example, a typical hard disk reads and writes data in disk blocks of 8K bytes; some types of hard disks may use other block sizes such as 16K and 64K. In order to take advantage of the block-level operations of such hard disks, a database server typically provides for data blocks of a size that is the same as, or a multiple of, the size of the disk blocks that are used by the hard disks. Further, in order to provide for faster data reads from and data writes to the hard disks, a database server copies each data block as an entire unit between the hard disks and the buffer cache in volatile memory. Unfortunately, database servers that use this approach for configuring and managing data blocks may encounter several problems.
One such problem is buffer cache underutilization and inefficient memory use by the database server. This is caused by the fact that most of the data processing by a database server is done at the data block level, while the actual access to the data in the buffer cache is done at the level of a data row and is therefore not block-based. For example, a database server copies entire data blocks into the buffer cache in volatile memory, writes back entire data blocks to the persistent storage devices, and compresses entire data blocks if the database server is capable of performing on-the-fly data compression. In contrast, most of the query and application access is row-based—that is, queries and applications read individual data rows, update individual data rows, delete individual rows, etc. Thus, even if only one of the data rows in a given data block is frequently accessed while the rest of the data rows are infrequently or almost never accessed, the database server would have to keep that data block in the buffer cache in order to provide efficient access to the frequently accessed data row even though this is not necessary for the rest of the data rows in that data block. This causes the buffer cache to be underutilized and essentially leads to inefficient use of volatile memory by the database server. The above problem is further exacerbated in operational contexts in which a database server may be configured to use storage mechanisms that deliberately spread a single data row across several data blocks in order to improve writing speed because this greatly increases the chances that frequently accessed data rows will be intermixed in the same data block with data rows that are not frequently accessed.
Another problem is that the above approach for configuring and managing data blocks is not easily susceptible to efficient storage optimizations. This is caused by the fact that currently, database servers allocate data rows to data blocks automatically without providing the users with any configurable row-level storage options. For example, currently some database servers allow users to specify storage and compression options for entire tables only, but do not allow users to specify such options at the granularity of a data row or even at the granularity of a data block. This prevents such database servers from being optimized for efficient storage and/or compression of data blocks, even when an application or a user (e.g., a database administrator) may have sufficient knowledge to determine that specific, individual data rows in a given table are not likely to be accessed in the near future.